package com.geotmt.billingcenter.modules.statis.mapper.provider;

import com.alibaba.druid.util.StringUtils;
import com.geotmt.billingcenter.common.entry.ConsumeStatisEntry;
import com.geotmt.billingcenter.common.vo.params.ParamsConsume;
import org.apache.ibatis.annotations.Param;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.text.MessageFormat;
import java.util.Date;
import java.util.List;

/**
 * @Description SQL类
 * @Author      yanghanwei
 * @Mail        yanghanwei@geotmt.com
 * @Date        2019/3/27 18:26
 * @Version     v1
 **/
public class ConsumeStatisProvider extends AbstractStatis {

    private static final Logger logger = LoggerFactory.getLogger(ConsumeStatisProvider.class);

    /**
     * 消耗统计(最细维度)
     * @param obj
     * @return
     */
    public String consumeStatisList(ParamsConsume obj){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT t1.day_flag dayFlag,t1.product_name productName,t1.company_name companyName,t1.cuser_name cuserName,t1.interface_name interfaceName,t1.isp isp,")
                .append(" count_query countQuery, count_get countGet, count_timeout countTimeout,count_charging countCharging,consume from (")
                .append("SELECT ")
                .append(this.preSqlConsume(obj,"con"))
                .append(" SUM(con.count_query) count_query,SUM(con.count_get) count_get,SUM(con.count_timeout) count_timeout,SUM(con.count_charging) count_charging, SUM(consume) consume")
                .append(" FROM fin_consume_statis con")
                .append(this.joinSqlConsume(obj,"con"))
                .append(" WHERE con.status=1")
                .append(whereSql(obj.getEffectStartTime(),obj.getEffectEndTime(),"con"))
                .append(whereSqlConsume(obj,"con"))
                .append(groupSqlConsume(obj,"con"))
                .append(") t1");
        //条件都不选的情况
        if(StringUtils.isEmpty(obj.getProductCode()) && null == obj.getCompanyId()
            && null == obj.getCuserId() && null == obj.getInterfaceId() && StringUtils.isEmpty(obj.getIsp())){
            sql = new StringBuffer();
            sql.append("SELECT t1.day_flag dayFlag, t1.product_name productName, t1.company_name companyName, t1.cuser_name cuserName, t1.interface_name interfaceName, t1.isp isp,")
                    .append(" count_query countQuery, count_get countGet, count_timeout countTimeout, count_charging countCharging, consume consume")
                    .append(" FROM")
                    .append(" (SELECT day_flag, con.product_code, product.product_name, con.company_id, com.company_full_name company_name, con.cuser_id, cuser.real_name cuser_name,")
                    .append(" con.interface_id, inter.interface_name, con.isp, con.count_query count_query, con.count_get count_get, con.count_timeout count_timeout,")
                    .append(" con.count_charging count_charging,con.consume consume ")
                    .append(" FROM")
                    .append(" fin_consume_statis con")
                    .append(" LEFT JOIN t_product product ON con.product_code = product.product_code")
                    .append(" LEFT JOIN t_company_info com ON con.company_id = com.id")
                    .append(" LEFT JOIN t_cuser_info cuser ON con.cuser_id = cuser.id")
                    .append(" LEFT JOIN t_interface inter ON con.interface_id = inter.id")
                    .append(" WHERE con.status=1")
                    .append(whereSql(obj.getEffectStartTime(),obj.getEffectEndTime(),"con"))
                    .append(" ) t1");
        }
        logger.info("消耗统计(最细维度)SQL:{}",sql);
        return sql.toString();
    }

    /**
     * 消耗list的总计(最细力度)
     * @param obj
     * @return
     */
    public String consumeStatisTotal(ParamsConsume obj){
        StringBuffer sql = new StringBuffer();
        String dayflag = formatDate(obj.getEffectStartTime(), obj.getEffectEndTime());
        sql.append("SELECT '").append(dayflag).append("' dayFlag,")
                .append(outerSelectConsume(obj,"t1"))
                .append(" t1.count_query countQuery, t1.count_get countGet, t1.count_timeout countTimeout, t1.count_charging countCharging,t1.consume consume")
                .append(" FROM")
                .append(" (SELECT day_flag,")
                .append(innerSelectConsume(obj))
                .append(" SUM(count_query) count_query, SUM(count_get) count_get, SUM(count_timeout) count_timeout, SUM(count_charging) count_charging, SUM(consume) consume")
                .append(" FROM")
                .append(" fin_consume_statis con")
                .append(" LEFT JOIN t_product product ON con.product_code = product.product_code")
                .append(" LEFT JOIN t_company_info com ON con.company_id = com.id")
                .append(" LEFT JOIN t_cuser_info cuser ON con.cuser_id = cuser.id")
                .append(" LEFT JOIN t_interface inter ON con.interface_id = inter.id")
                .append(" WHERE con.status=1")
                .append(whereSql(obj.getEffectStartTime(),obj.getEffectEndTime(),"con"))
                .append(whereSqlConsume(obj,"con"))
                .append(totalConsumeGroup(obj))
                .append(" ) t1");
        logger.info("消耗list的总计(最细力度) SQL:",sql);
        return sql.toString();
    }

    /**
     * 消耗历史的量统计
     * @param obj
     * @return
     */
    public String hisConsumeStatis(ParamsConsume obj){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT #{cuserId} cuserId,#{interfaceId} interfaceId, SUM(t.count_charging) countCharging")
                .append(" FROM")
                .append(" (SELECT count_charging")
                .append(" FROM fin_consume_statis con")
                .append(" WHERE con.status=1  AND cuser_id=#{cuserId} AND interface_id=#{interfaceId}")
                .append(whereSql(obj.getEffectStartTime(),obj.getEffectEndTime(),"con"))
                .append(" ) t");
        logger.info("获取消耗历史数量 SQL:{}",sql);
        return sql.toString();
    }

    /**
     * 消耗统计(到客户维度)
     * @param obj
     * @return
     */
    public String consumeStatisCompanyList(ParamsConsume obj){
        StringBuffer sql = new StringBuffer();

        return sql.toString();
    }

    /**
     * 删除某天的消耗统计
     * @param dayFlag
     * @return
     */
    public String deleteConsumeStatisForDay(@Param(value = "dayFlag") String dayFlag,
                                            @Param(value = "deleteTime") Date deleteTime){
        StringBuffer sql = new StringBuffer();
        sql.append("update fin_consume_statis set status=2,delete_time=#{deleteTime} WHERE ");
        if(!StringUtils.isEmpty(dayFlag)){
            sql.append(" day_flag=#{dayFlag}");
        }else{
            sql.append(" 1=0");
        }
        logger.info("删除某天的消耗统计SQL:{}",sql);
        return sql.toString();
    }

    /**
     * 删除某段时间的消耗统计
     * @param startDay
     * @param endDay
     * @return
     */
    public String deleteConsumeStatisForDate(@Param(value = "startDay") String startDay,
                                             @Param(value = "endDay") String endDay,
                                             @Param(value = "deleteTime") Date deleteTime ){
        StringBuffer sql = new StringBuffer();

        sql.append("updat fin_consume_statis set status='2',delete_time=#{deleteTime} WHERE ");
        if(!StringUtils.isEmpty(startDay) && !StringUtils.isEmpty(endDay)){
            sql.append(" day_flag>=#{startDay} and day_flag<=#{endDay}");
        }else{
            sql.append(" 1=0");
        }
        logger.info("删除某段时间的消耗统计SQL:{}",sql);
        return sql.toString();
    }

    /**
     * 更新统计报表中的相关维度id信息
     * @param startDay
     * @param endDay
     * @param id
     * @return
     */
    public String updateConsumeStatis(@Param(value = "startDay") String startDay,
                                      @Param(value = "endDay") String endDay,
                                      @Param(value = "id") Integer id){
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE `fin_consume_statis` a,")
                .append(" (SELECT tci.id cuser_id, tci.company_id, tp.`product_code`")
                .append(" FROM t_cuser_info tci")
                .append(" LEFT JOIN `t_customer_product` tcp ON tci.`company_id` = tcp.`customer_id`")
                .append(" LEFT JOIN t_product tp ON tcp.`product_id` = tp.`id`) b,")
                .append(" t_interface c")
                .append(" SET a.product_code = b.product_code, a.`company_id` = b.company_id, a.`interface_id`= c.id")
                .append(" WHERE a.`cuser_id` = b.cuser_id AND a.`interface_type`=c.`interface_name`");
        if(!StringUtils.isEmpty(startDay) && !StringUtils.isEmpty(endDay)){
            sql.append(" AND a.day_flag>=#{startDay} AND a.day_flag<=#{endDay}");
        }
        if(null != id){
            sql.append(" AND a.id=#{id}");
        }
        logger.info("更新统计报表中的相关维度id信息SQL:{}",sql);
        return sql.toString();
    }

    /**
     * 更新某天的金额
     * @param cuserId
     * @param interfaceId
     * @param dayFlag
     * @param money
     * @return
     */
    public String updateConsumeMoney(@Param(value = "cuserId") Integer cuserId,
                                     @Param(value = "interfaceId") Integer interfaceId,
                                     @Param(value = "dayFlag") String dayFlag,
                                     @Param(value = "money") Double money){
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE `fin_consume_statis` SET consume=#{money}")
                .append(" WHERE cuser_id=#{cuserId}")
                .append(" AND interface_id=#{interfaceId}")
                .append(" AND day_flag=#{dayFlag}");
        logger.info("更新某天的金额SQL:{}",sql);
        return sql.toString();
    }


    /**
     * 更新某天的金额
     * @param cuserId
     * @param interfaceType
     * @param dayFlag
     * @param money
     * @return
     */
    public String updateConsumeMoney2(@Param(value = "cuserId") Integer cuserId,
                                     @Param(value = "interfaceType") String interfaceType,
                                     @Param(value = "dayFlag") String dayFlag,
                                     @Param(value = "money") Double money){
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE `fin_consume_statis` SET consume=#{money}")
                .append(" WHERE cuser_id=#{cuserId}")
                .append(" AND interface_type=#{interfaceType}")
                .append(" AND day_flag=#{dayFlag}");
        logger.info("更新某天的金额SQL:{}",sql);
        return sql.toString();
    }

    /**
     * 批量插入统计数据
     * @return
     */
    public String batchInsertStatis(@Param(value = "subConsumeStatisList")List<ConsumeStatisEntry> subConsumeStatisList){
        StringBuffer sql = new StringBuffer();
        StringBuffer batchSql = new StringBuffer();
        sql.append("INSERT INTO `fin_consume_statis`(cuser_id, interface_id, isp,count_query,count_get,count_timeout,count_charging,interface_type,day_flag,status) VALUES");
        if(null != subConsumeStatisList && !subConsumeStatisList.isEmpty()){
            int size = subConsumeStatisList.size();
            batchSql.append("(#'{'subConsumeStatisList[{0}].cuserId},#'{'subConsumeStatisList[{0}].interfaceId},#'{'subConsumeStatisList[{0}].isp}")
                    .append(",#'{'subConsumeStatisList[{0}].countQuery},#'{'subConsumeStatisList[{0}].countGet},#'{'subConsumeStatisList[{0}].countTimeout}")
                    .append(",#'{'subConsumeStatisList[{0}].countCharging},#'{'subConsumeStatisList[{0}].interfaceType},#'{'subConsumeStatisList[{0}].dayFlag}")
                    .append(",1)");
            MessageFormat mf = new MessageFormat(  batchSql.toString());
            for (int i = 0; i < size; i++) {
                sql.append(mf.format(new Object[]{i}));
                if (i < size - 1) {
                    sql.append(",");
                }
            }
        }
        logger.info("批量插入统计数据SQL：{}",sql);
        return sql.toString();
    }

}
